BEFORE UPDATE Trigger

This MariaDB tutorial explains how to create a BEFORE UPDATE Trigger in MariaDB with syntax and examples.

Description

A BEFORE UPDATE Trigger means that MariaDB will fire this trigger before the UPDATE operation is executed.

Syntax

The syntax to create a BEFORE UPDATE Trigger in MariaDB is:

CREATE TRIGGER trigger_name

BEFORE UPDATE

   ON table_name FOR EACH ROW

 

BEGIN

 

   -- variable declarations

 

   -- trigger code

 

END;

Parameters or Arguments

trigger_name

The name of the trigger to create.

BEFORE UPDATE

It indicates that the trigger will fire before the UPDATE operation is executed.

table_name

The name of the table that the trigger is created on.

Restrictions

• You can not create a BEFORE trigger on a view.
• You can update the NEW values.
• You can not update the OLD values.

Note

• See also how to create AFTER DELETE, AFTER INSERT, AFTER UPDATE, BEFORE DELETE, and BEFORE INSERT triggers.
• See also how to drop a trigger.

Example

Let's look at an example of how to create an BEFORE UPDATE trigger using the CREATE TRIGGER statement in MariaDB.

If you had a table created as follows:

CREATE TABLE contacts

( contact_id INT(11) NOT NULL AUTO_INCREMENT,

  last_name VARCHAR(30) NOT NULL,

  first_name VARCHAR(25),

  birthday DATE,

  created_date DATE,

  created_by VARCHAR(30),

  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)

);

We could then use the CREATE TRIGGER statement to create an BEFORE UPDATE trigger as follows:

DELIMITER //

 

CREATE TRIGGER contacts_before_update

BEFORE UPDATE

   ON contacts FOR EACH ROW

 

BEGIN

 

   DECLARE vUser varchar(50);

 

   -- Find username of person performing the INSERT into table

   SELECT USER() INTO vUser;

 

   -- Insert record into audit table

   INSERT INTO contacts_audit

   ( contact_id,

     updated_date,

     updated_by)

   VALUES

   ( NEW.contact_id,

     SYSDATE(),

     vUser );

 

END; //

 

DELIMITER ;